Conceitos Gerais

O SQL Server coloca todos seus processos em fila para serem processados. Portanto, pode parecer que 2 queries são executadas no mesmo momento mas é mera ilusão, foram executadas em momentos diferentes e isto evita muito o conflito na utilização dos dados.

Portanto o MS SQL server processa a pesquisa do usuário a e depois a do b. Em situações normais, o usuário b só obterá as informações que o usuário a pode ter alterado depois que elas forem gravadas no disco e estiverem disponíveis para todos. Mas o SQL divide o tempo de execução e se uma pesquisa está sendo custosa para ele, para não deixar os demais usuários na mão, ela para temporariamente a execução da tarefa A e vai executar a tarefa B. É aí que pode ocorrer os locks de tabela.

Metodologia de funcionamento do Microsoft SQL Server.

A metodologia de funcionamento do Microsoft SQL Server pode ser resumida em uma sigla para melhor memorização:

ACID

ACID é uma sigla para atomicidade, consistência, isolamento e durabilidade.
É um conceito que os profissionais de banco de dados geralmente procuram ao avaliar bancos de dados e arquiteturas de aplicativos.
Para um banco de dados confiável, todos esses quatro atributos devem ser alcançados.

1-A atomicidade é uma proposição de tudo ou nada.
Nesta proposição temos a regra : O Microsoft SQL Server está num estado confiável e após o término desta operação deverá voltar a um estado confiável. Se por acaso houver algum problema no processo ( uma consistencia, qualquer erro), a ação feita deverá ser revertida em busca do estado estável e confiável do servidor.
Ou o processo termina como um todo perfeito ou volta atrás e nada faz.

2-A consistência garante que uma transação nunca deixe seu banco de dados em um estado pela metade.
Junto com a atomicidade esta proposição garantirá que tudo esta correto antes e depois da operação.
O SQL Server esta num estado consistente antes da operação e estará consistente depois da operação.
Para isso, por exemplo, num Insert numa tabela talvez seje necessário reconstruir seus índices e o MS SQL SRV faz isso automaticamente para manter a consistencia do indice.

3-O isolamento mantém as transações separadas uma da outra até que elas sejam concluídas.
Para implementar esta funcionalidade o MS SQL SRV faz com que todas suas tarefas sejam serializadas, uma após a outra sendo que uma jamais saberá o que a outra está fazendo porque são executadas em momentos diferentes e completamente isoladas uma da outra.
Contudo, se uma tarefa é muito demorada, com grande custo, nada impede do sistema operacional tomar as redeas do sistema e/ou do MS SQL SERVER atender outra tarefa mesmo sem ter terminado a anterior fatiando seu tempo entre algumas requisições.
Outra situação que pode ocorrer (quebra do sequenciamento perfeito) é o checkpoint.
O checkpoint é uma interrupção que ocorre de tempos em tempos (segundos) que envia tudo que pode ser enviado ao disco. Como um watchdog. O checkpoint ocorre em períodos de tempo de alguns segundos e tem um limite de duração para terminar.
Se uma transação ainda não terminou a ocorrência do checkpoint pode interromper sua execução para a gravação no disco e com isto dar um lock nos processos do sistema.
Contudo, a tarefa anterior é executada completamente isolada da atual e vice-versa.
Muito cuidado com a diferença da querie hint NO_LOG do servidor Oracle para o servidor MS SQL Server porque são totalmente diferentes. Os dois servidores tem a mesma querie hint mas usam ela de maneira completamente diferente como será explicado abaixo.

4-A durabilidade garante que o banco de dados acompanhe as alterações pendentes de forma que o servidor possa se recuperar de uma finalização anormal.
Para implementar esta funcionalidade o servidor grava no log ( WRITE LOG AHEAD ) tudo que faz assim sabe como voltar o processo no caso de erro.

Isolamento - Detalhes

Notas :
   T1, T2...São transações.
   C() é uma constraint qualquer.
   r é leitura(read) e w é escrita(w).

A norma ANSI SQL-92 define os níveis de isolamento:
1-Dirty Reads ( leituras sujas),
2-Non-Reapeatable Reads (leituras não repetíveis)
3-Phantoms ( fantasmas ).
Estas definições da ANSI SQL falham em caracterizar vários níveis de isolamento populares, incluindo as implementações de bloqueio padrão dos níveis.

1. Introdução

A execução de transações simultâneas em diferentes níveis de isolamento permite que os designers de aplicativos negociem a taxa de transferência pelo método mais correto para aquela situação.
Níveis de isolamento mais baixos aumentam a ocorrência de transações, mas correm o risco de mostrar às transações um banco de dados confuso ou incorreto.
Surpreendentemente, algumas transações podem ser executadas no nível de isolamento mais alto (serialização perfeita), enquanto transações simultâneas em execução em um nível de isolamento mais baixo podem acessar estados que ainda não foram confirmados ou que pós-data declaram a transação lida anteriormente.
Obviamente, transações executadas em níveis mais baixos de isolamento podem produzir dados inválidos.
Os projetistas de aplicativos devem impedir que transações posteriores em níveis de isolamento mais altos acessem esses dados inválidos e propaguem erros.

Definições de isolamento

2.1 Conceitos de serialização Uma transação agrupa um conjunto de ações que transformam o banco de dados de um estado consistente para outro.
Chama-se serialização perfeita quando um processo começa e acaba antes que o próximo processo comece.
No entanto, como o windows é um sistema voltado a eventos, um processo pode ser interrompido por outro processo de maior prioridade e depois desse processo 'atendido', o sistema retorna aonde havia parado.
Diz-se que duas ações em um histórico entram em conflito se forem executadas por transações distintas no mesmo item de dados e pelo menos uma delas é uma ação de gravação.
Após essa definição adota uma ampla interpretação de “item de dados”: poderia ser uma linha da tabela, uma página, uma tabela inteira ou uma mensagem em uma fila.
Ações conflitantes também podem ocorrer em um conjunto de itens de dados, coberto por um bloqueio, bem como em um único item de dados.

2.2 Níveis de isolamento SQL ANSI Os designers de isolamento ANSI SQL buscavam uma definição que admitisse muitas implementações diferentes, não apenas bloqueando.
Eles definiram o isolamento com os três fenômenos a seguir:

Condição P1 (Dirty Read - Leitura suja)
1-A transação T1 modifica um item de dados.
2-Outra transação T2 lê esse item de dados antes de T1 executar um COMMIT ou ROLLBACK.
3-Se T1 executar um ROLLBACK, T2 lerá um item de dados que nunca foi confirmado e, portanto, nunca existiu.
Nota : O Query hint NO_LOCK no MS SQL Server pode causar este problema.

Condição P2 (Non-repeatable or Fuzzy Read - Leitura não repetível ou difusa):
1-A transação T1 lê um item de dados.
2-Outra transação T2 modifica ou exclui esse item de dados e confirma.
3-Se T1, em seguida, tenta reler o item de dados, ele recebe um valor modificado ou descobre que o item de dados foi excluído.

Condição P3 (Phantom - Fantasma)
1-A transação T1 lê um conjunto de itens de dados que satisfazem algumas <condições de pesquisa>.
2-A Transação T2 em seguida, cria itens de dados que atendem à <condição de pesquisa> de T1 e confirma.
3-Se T1 repetir sua leitura com a mesma <condição de pesquisa>, ele obterá um conjunto de itens de dados diferentes da primeira leitura.

Condição P4C - (Lost Update - atualização perdida )
O nível de isolamento READ COMMITTED se estende o bloqueio para cursores SQL, adicionando uma nova ação de leitura para FETCH a partir de um cursor e exigindo que um bloqueio seja mantido no item atual do cursor.
O bloqueio é mantido até que o cursor se mova ou seja fechado, possivelmente por uma confirmação.
Naturalmente, a transação de busca pode atualizar a linha e, nesse caso, um bloqueio de gravação será mantido na linha até que a transação seja confirmada, mesmo depois que o cursor se mover com uma busca subsequente.
A notação é estendida para incluir, rc significando cursor de leitura, e wc, significando escrever o registro atual do cursor. Um rc1[x] e um wc1 posterior[x] impedem um w2 de intervenção intermediária [x].
O fenômeno P4, renomeado P4C, é evitado neste caso.

Condição P0 - (Dirty Write - Gravação suja)
1-A transação T1 modifica um item de dados.
2-Outra transação T2 modifica ainda mais esse item de dados antes que T1 execute um COMMIT ou ROLLBACK.
Se T1 ou T2 executar um ROLLBACK, não está claro qual deve ser o valor correto dos dados.

Condição A5 (Data Item Constraint Violation - Violação de restrição de item de dados)
1-Suponha que C() seja uma restrição de banco de dados entre dois itens de dados x e y no banco de dados.

Condição A5A - Read Skew
1-Suponha que a transação T1 leia x.
2-Então uma segunda transação T2 atualize x e y para novos valores e dê o commit.
2-Se agora T1 ler y, ele poderá ver um estado inconsistente e, portanto, produzir um estado inconsistente como saída.

Condição A5B - Write Skew
1-Suponha que T1 leia x e y, que sejam consistentes com C()
2-Então uma T2 leia x e y, escreva x e confirme.
3-Então T1 escreve y.
4-Se houver uma restrição entre x e y, isso poderá ser violado.

SnapShot Isolation - Isolamento de instantâneo
Neste tipo de isolamento cada transação lê dados de um instantâneo dos dados (confirmados) a partir do momento em que a transação iniciou, denominada Start-Timestamp.
Esse período pode ser a qualquer momento antes da primeira leitura da transação.
Uma transação em execução no isolamento de instantâneo nunca é bloqueada na tentativa de leitura, desde que os dados do instantâneo de seu carimbo de data / hora inicial possam ser mantidos.
As gravações da transação (atualizações, inserções e exclusões) também serão refletidas neste instantâneo, para serem lidas novamente se a transação acessar (ou seja, ler ou atualizar) os dados uma segunda vez.
As atualizações de outras transações ativas após a transação Start-Timestamp são invisíveis para a transação.

O problema com o isolamento de instantâneo é que não pode ser serializado porque as leituras de uma transação ocorrem em um instante e as gravações em outro.

Violação de Constraints
Os bancos de dados individuais satisfazem as restrições de vários itens de dados (por exemplo, exclusividade de chaves, integridade referencial, replicação de linhas em duas tabelas, etc.).
Juntos, eles formam uma constraint(restrição) invariável da base de dados, C(DB).
As transações devem preservar o predicado de restrição para manter a consistência: se o banco de dados for consistente quando a transação for iniciada, o banco de dados será consistente quando a transação for confirmada.
Se uma transação lê um estado de banco de dados que viola o predicado de restrição, a transação sofre uma anomalia de concorrência de violação de restrição .
Tais violações de restrição são chamadas de análise inconsistentes..

Fuzzy Reads
As leituras difusas (P2) são uma forma degenerada de inclinação de leitura, em que x = y.
Mais tipicamente, uma transação lê dois itens diferentes, mas relacionados (por exemplo, integridade referencial).
O Write Skew (A5B) pode surgir de uma restrição em um banco, onde os saldos das contas podem ficar negativos desde que a soma dos saldos mantidos em comum permaneça não-negativa, com uma anomalia ocorrendo como no histórico H5.

Tabela 4. Tipos de isolamento caracterizados por possíveis anomalias permitidas.

Nível de Isolamento	P0 - Dirty Write	P1 - Dirty Read	P4C - Cursor Lost Update	P4 - Lost Update	P2 - Fuzzy Read	P3 - Phantom	A5A - Read Skew	A5B - Write Skew
Read Uncommited==Degree 1	Impossível	Possível	Possível	Possível	Possível	Possível	Possível	Possível
Read Commited==Degree 2	Impossível	Impossível	Possível	Possível	Possível	Possível	Possível	Possível
Cursor Stability	Impossível	Impossível	Impossível	As vezes, possível	As vezes, possível	Possível	Possível	As vezes, possível
Repeatable Read	Impossível	Impossível	Impossível	Impossível	Impossível	Possível	Impossível	Impossível
SnapShot	Impossível	Impossível	Impossível	Impossível	Impossível	Algumas vezes possível	Impossível	Possível
ANSI SQL Serializable-Degree 3-Repeatable Read	Impossível	Impossível	Impossível	Impossível	Impossível	Impossível	Impossível	Impossível